package com.Druid1;

import java.lang.reflect.Field;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.core.ValueOperations;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;

import com.alibaba.druid.filter.FilterChain;
import com.alibaba.druid.filter.FilterEventAdapter;
import com.alibaba.druid.mock.MockResultSet;
import com.alibaba.druid.mock.MockResultSetMetaData;
import com.alibaba.druid.proxy.jdbc.JdbcParameter;
import com.alibaba.druid.proxy.jdbc.PreparedStatementProxy;
import com.alibaba.druid.proxy.jdbc.ResultSetProxy;
import com.alibaba.druid.proxy.jdbc.ResultSetProxyImpl;
import com.alibaba.druid.proxy.jdbc.StatementProxy;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLBetweenExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOperator;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLInListExpr;
import com.alibaba.druid.sql.ast.expr.SQLIntegerExpr;
import com.alibaba.druid.sql.ast.expr.SQLVariantRefExpr;
import com.alibaba.druid.sql.ast.statement.SQLExprTableSource;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.ast.statement.SQLTableSource;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlDeleteStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlASTVisitorAdapter;
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.druid.util.jdbc.ResultSetMetaDataBase.ColumnMetaData;

@Component
public class MyDataSourceFilter extends FilterEventAdapter {
	/**
	 * 缓存默认过期时间
	 */
	public final static int DEFAULT_CACHE_TIMEOUT = 60;
	public final static List<SQLBinaryOperator> ops = Arrays.asList(
			SQLBinaryOperator.BooleanAnd, SQLBinaryOperator.BooleanOr);
	@Autowired
	RedisTemplate<String, Object> redisTemplate;
	boolean debug = true;

	@Override
	public ResultSetProxy preparedStatement_executeQuery(FilterChain chain,
			PreparedStatementProxy statement) throws SQLException {
		if (debug) {
			System.out.println(statement.getParameters());
		}

		return handleQuery(chain, statement, statement.getSql());
	}

	@Override
	public ResultSetProxy statement_executeQuery(FilterChain chain,
			StatementProxy statement, String sql) throws SQLException {
		return handleQuery(chain, statement, sql);
	}

	@Override
	public int statement_executeUpdate(FilterChain chain,
			StatementProxy statement, String sql) throws SQLException {
		return handleUpdate(chain, statement, sql);
	}

	@Override
	public int preparedStatement_executeUpdate(FilterChain chain,
			PreparedStatementProxy statement) throws SQLException {
		return handleUpdate(chain, statement, statement.getSql());
	}

	@Override
	public boolean statement_execute(FilterChain chain,
			StatementProxy statement, String sql) throws SQLException {
		if (debug) {
			System.out.println("MyDataSourceFilter.statement_execute()");
		}
		int ret = handleUpdate(chain, statement, sql);
		return ret > 0;
	}

	/**
	 * 处理查询sql
	 * 
	 * @param chain
	 * @param statement
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	private ResultSetProxy handleQuery(FilterChain chain,
			StatementProxy statement, String sql) throws SQLException {
		try {
			// 返回结果集
			ResultSetProxy resultSet = null;
			// System.out.println(sql);
			// -----------parser解析sql---------------
			SQLStatement sqlStatement = SQLUtils.parseStatements(sql,
					JdbcConstants.MYSQL).get(0);
			SQLSelectStatement selectStatement = (SQLSelectStatement) sqlStatement;
			MySqlSelectQueryBlock query = (MySqlSelectQueryBlock) selectStatement
					.getSelect().getQuery();
			// -----------parser解析sql---------------
			// 如果不是单表查询
			if (!isSingleTableQuery(query)) {
				System.out.println("非单表查询    不进行缓存");
				// 真正查询mysql
				return realQueryMysql(chain, statement, sql);
			}
			// 获取 from 后面的表名
			String tableName = getTableName(query.getFrom());
			// 根据表名查询bean 获取id名称
			String idName = getIdName(tableName);
			SQLExpr where = query.getWhere();

			// 是否缓存命中
			boolean isCache = false;
			// where 参数列表是否包含id字段
			boolean containsId = false;
			Par idPar = null;
			if (where != null) {

				int idIndex = getIdIndex(sqlStatement);
				// 获取where 的参数列表
				List<Par> wherePars = getWherePars(where, statement, idIndex);
				// 确保只有一个参数
				if (wherePars.size() != 1) {
					System.out.println("where参数个数数量不为1  不进行缓存");
					// 真正查询mysql
					return realQueryMysql(chain, statement, sql);
				}
				// 业务逻辑假设所有表的主键名称都是 id
				// 获取id
				idPar = getIdPar(wherePars);
				if (idPar != null) {
					containsId = true;
					Object bean = null;
					try {
						// 查询缓存
						bean = GetFromRedis(idPar, tableName);
					} catch (Exception e) {
						if (debug) {
							System.out.println("无缓存");
						}
					}
					// 缓存命中!!!
					if (bean != null) {
						isCache = true;
						Integer id = (Integer) idPar.getValue();
						String key = getRedisKey(id, tableName,
								idPar.getParName());
						redisTemplate.expire(key, DEFAULT_CACHE_TIMEOUT,
								TimeUnit.SECONDS);
						// 根据redis缓存数据 构造假的数据集返回
						MyMockResultSet mockResultSet = new MyMockResultSet(
								bean);
						resultSet = new ResultSetProxyImpl(statement,
								mockResultSet, 1, sql);
						if (debug) {
							System.out.println("从redis获取数据 : " + bean);
						}
					}
				}
			}
			// 没有缓存
			if (!isCache) {
				// 真正查询mysql
				resultSet = realQueryMysql(chain, statement, sql);
				if (containsId) {
					// sql查询结果 添加到redis
					try {
						Assert.notNull(idPar);
						AddToRedis(resultSet, tableName, idPar.getParName());
					} catch (Exception e) {
						if (debug) {
							System.out.println("[ERROR]	AddToRedis 添加缓存失败");
						}
					}
				}
			}
			return resultSet;

		} catch (SQLException error) {
			throw error;
		} catch (RuntimeException error) {
			throw error;
		} catch (Error error) {
			throw error;
		}
	}

	/**
	 * 处理update delete sql
	 * 
	 * @param chain
	 * @param statement
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	private int handleUpdate(FilterChain chain, StatementProxy statement,
			String sql) throws SQLException {
		SQLStatement sqlStatement = SQLUtils.parseStatements(sql,
				JdbcConstants.MYSQL).get(0);
		if (!(sqlStatement instanceof MySqlInsertStatement)) {

			int idIndex = getIdIndex(sqlStatement);

			// sql update 语句
			if (sqlStatement instanceof MySqlUpdateStatement) {
				MySqlUpdateStatement updateStatement = (MySqlUpdateStatement) sqlStatement;
				SQLExpr where = updateStatement.getWhere();
				// 清理缓存
				delRedisCache(where, statement,
						updateStatement.getTableSource(), idIndex);
			} else
			// sql delete 语句
			if (sqlStatement instanceof MySqlDeleteStatement) {
				MySqlDeleteStatement deleteStatement = (MySqlDeleteStatement) sqlStatement;
				SQLExpr where = deleteStatement.getWhere();
				// 清理缓存
				delRedisCache(where, statement,
						deleteStatement.getTableSource(), idIndex);
			}
		}
		// 执行sql
		if (statement instanceof PreparedStatementProxy) {
			return chain
					.preparedStatement_executeUpdate((PreparedStatementProxy) statement);
		} else {
			return chain.statement_executeUpdate(statement, sql);
		}
	}

	/**
	 * 根据表名查询bean 获取id名称
	 * 
	 * @param tableName
	 * @return
	 */
	private String getIdName(String tableName) {
		Class<?> beanClazz = null;
		try {
			// 根据表名获取bean的clazz 默认表名和类名相同
			beanClazz = ClassHelper.getClassByName(tableName);
			if (beanClazz == null) {
				if (debug) {
					System.out.println("加载" + tableName + "失败");
				}
			}
			Field[] fields = beanClazz.getDeclaredFields();
			// 找到id
			for (Field field : fields) {
				field.setAccessible(true);
				String name = field.getName();
				if (isId(name)) {
					return name;
				}

			}
		} catch (Exception e) {
			System.out.println("无法加载" + tableName + "对应的class");
		}

		return null;
	}

	private boolean isId(String name) {
		if ("id".equalsIgnoreCase(name)) {
			return true;
		}
		int len = name.length();
		if (len >= 2 && name.substring(len - 2).equalsIgnoreCase("Id")) {
			return true;
		}
		return false;
	}

	/**
	 * 真正查询数据库
	 * 
	 * @param chain
	 * @param statement
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	private ResultSetProxy realQueryMysql(FilterChain chain,
			StatementProxy statement, String sql) throws SQLException {
		ResultSetProxy resultSet;
		if (statement instanceof PreparedStatementProxy) {
			resultSet = chain
					.preparedStatement_executeQuery((PreparedStatementProxy) statement);
		} else {
			resultSet = chain.statement_executeQuery(statement, sql);
		}
		return resultSet;
	}

	/**
	 * 是否为单表查询
	 * 
	 * @param query
	 * @return
	 */
	private boolean isSingleTableQuery(MySqlSelectQueryBlock query) {
		SQLTableSource from = query.getFrom();
		if (from instanceof SQLExprTableSource) {
			return true;
		}

		return false;
	}

	/**
	 * sql查询结果 添加到redis
	 * 
	 * @param resultSet
	 * @param tableName
	 * @param idName
	 * @throws SQLException
	 * @throws NoSuchFieldException
	 */
	private void AddToRedis(ResultSetProxy resultSet, String tableName,
			String idName) throws SQLException, NoSuchFieldException {
		// 根据表名获取bean的class 默认表名和bean名称相同
		Class<?> beanClazz = ClassHelper.getClassByName(tableName);
		if (beanClazz == null) {
			System.out.println("找不到类" + tableName + "class");
		}
		BeanHandler<?> handler = new BeanHandler<>(beanClazz);
		try {
			Object bean;
			Field idField = beanClazz.getDeclaredField(idName);
			idField.setAccessible(true);

			while ((bean = handler.handle(resultSet)) != null) {
				Integer id = idField.getInt(bean);
				Assert.notNull(id);
				ValueOperations<String, Object> op = redisTemplate
						.opsForValue();
				String key = getRedisKey(id, tableName, idName);
				// 数据插入redis
				op.set(key, bean);
				// 设置缓存过期时间
				redisTemplate.expire(key, DEFAULT_CACHE_TIMEOUT,
						TimeUnit.SECONDS);
				if (debug) {
					System.out.println(key + "  插入redis");
				}
			}
		} catch (SQLException e) {
			throw e;
		} catch (NoSuchFieldException e) {
			throw e;
		} catch (SecurityException e) {
			throw e;
		} catch (IllegalArgumentException e) {
			throw e;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		// 回复原点
		while (resultSet.previous())
			;
	}

	/**
	 * 获取id对应的变量where id=? 所在下标位置
	 * 
	 * @param sqlStatement
	 * @return
	 */
	private int getIdIndex(SQLStatement sqlStatement) {
		AtomicInteger ret=new AtomicInteger(0);
		sqlStatement.accept(new MySqlASTVisitorAdapter() {
			public int index=0;
			public int idIndex=0;
			public boolean isFind=false;
			@Override
			public void endVisit(SQLVariantRefExpr x) {
				// 已经找到id的位置
				if (isFind) {
					return;
				}
				SQLObject parent = x.getParent();
				if (parent != null
						&& parent.getClass().equals(SQLBinaryOpExpr.class)) {
					SQLBinaryOpExpr binaryOpExpr = (SQLBinaryOpExpr) parent;
					if (binaryOpExpr.getOperator().equals(
							SQLBinaryOperator.Equality)) {
						String columnName = ((SQLIdentifierExpr) binaryOpExpr
								.getLeft()).getName();

						if (isId(columnName)) {

							idIndex=index;
							isFind=true;
							ret.set(idIndex);
						}
					}
				}
				index++;
			}
		});
		return ret.intValue();
	}

	/**
	 * 清理缓存
	 * 
	 * @param where
	 * @param statement
	 * @param sqlTableSource
	 * @param idIndex
	 */
	private void delRedisCache(SQLExpr where, StatementProxy statement,
			SQLTableSource sqlTableSource, int idIndex) {
		String tableName = getTableName(sqlTableSource);
		String idName = getIdName(tableName);
		List<Par> wherePars = getWherePars(where, statement, idIndex);
		Par idPar = getIdPar(wherePars);
		Integer id = (Integer) idPar.getValue();
		String key = getRedisKey(id, tableName, idName);
		Assert.notNull(tableName);
		Assert.notNull(wherePars);
		Assert.notNull(idPar);
		// 清除redis缓存
		redisTemplate.delete(key);
	}

	/**
	 * 重redis获取缓存
	 * 
	 * @param idPar
	 * @param tableName
	 * @return
	 */
	private Object GetFromRedis(Par idPar, String tableName) {
		Integer id = (Integer) idPar.getValue();
		String key = getRedisKey(id, tableName, idPar.getParName());
		ValueOperations<String, Object> ops = redisTemplate.opsForValue();
		Object bean = ops.get(key);
		return bean;
	}

	/**
	 * 生成redis的key
	 * 
	 * @param id
	 * @param tableName
	 * @param idName
	 * @return
	 */
	private String getRedisKey(Integer id, String tableName, String idName) {
		String key = tableName + ":" + idName + ":" + id;
		return key;
	}

	private Par getIdPar(List<Par> wherePars) {
		Par idPar = null;
		for (Par par : wherePars) {
			if (isId(par.getParName())) {
				idPar = par;
			}
		}
		return idPar;
	}

	/**
	 * 获取sql 的 where参数
	 * 
	 * @param where
	 * @param idIndex
	 * @param ps
	 * @return
	 */
	private List<Par> getWherePars(SQLExpr where, Statement statement,
			int idIndex) {
		PreparedStatementProxy ps = null;
		if (statement instanceof PreparedStatementProxy) {
			ps = (PreparedStatementProxy) statement;
		}
		List<Par> whereParNames = new ArrayList<>();
		// AtomicInteger index = new AtomicInteger(0);
		// 遍历 where 的 Sql AST 获取参数列表
		preOrder(where, whereParNames, ps == null ? null : ps.getParameters(),
				idIndex);

		return whereParNames;
	}

	/**
	 * 获取表名
	 * 
	 * @param query
	 * @return
	 */
	private String getTableName(SQLTableSource sqlTableSource) {
		SQLExprTableSource source = (SQLExprTableSource) sqlTableSource;
		String tableName = ((SQLIdentifierExpr) source.getExpr()).getName();
		int start = 0;
		int end = tableName.length();
		if (tableName.charAt(0) == '`') {
			start = 1;
		}
		if (tableName.charAt(end - 1) == '`') {
			end = end - 1;
		}
		if (start != 0 || end != tableName.length()) {
			tableName = tableName.substring(start, end);
		}
		return tableName;
	}

	/**
	 * 递归遍历AST获取参数
	 * 
	 */
	private void preOrder(SQLExpr expr, List<Par> parNames,
			Map<Integer, JdbcParameter> map, int idIndex) {
		if (expr != null && expr instanceof SQLBinaryOpExpr) {
			SQLBinaryOpExpr opExpr = (SQLBinaryOpExpr) expr;
			preOrder(opExpr.getLeft(), parNames, map, idIndex);
			preOrder(opExpr.getRight(), parNames, map, idIndex);
		}
		if (expr instanceof SQLBinaryOpExpr) {
			SQLBinaryOpExpr sqlBinaryOpExpr = (SQLBinaryOpExpr) expr;
			SQLBinaryOperator currOper = sqlBinaryOpExpr.getOperator();

			if (!ops.contains(currOper)) {
				// currOps: where id =1
				if (debug) {
					System.out.println("opExpr=" + expr + "  "
							+ sqlBinaryOpExpr.getLeft()
							+ sqlBinaryOpExpr.getOperator()
							+ sqlBinaryOpExpr.getRight());
				}
				SQLExpr right = sqlBinaryOpExpr.getRight();
				Par par = new Par();
				String parName = sqlBinaryOpExpr.getLeft().toString();
				// 获取sql语句的参数
				par.setParName(parName);
				if (right instanceof SQLIntegerExpr) {
					SQLIntegerExpr integerExpr = (SQLIntegerExpr) right;
					Integer id = (Integer) integerExpr.getNumber();
					par.setValue(id);

				} else if (right instanceof SQLVariantRefExpr/* sql 中的? 值从参数中获取 */) {
					JdbcParameter idParameter = map.get(idIndex);
					Integer id = (Integer) idParameter.getValue();
					par.setValue(id);
				}
				parNames.add(par);
			}
		} else if (expr instanceof SQLBetweenExpr) {
			SQLBetweenExpr betweenExpr = (SQLBetweenExpr) expr;
			SQLIdentifierExpr name = (SQLIdentifierExpr) betweenExpr
					.getTestExpr();
			SQLExpr begin = betweenExpr.getBeginExpr();
			SQLExpr end = betweenExpr.getEndExpr();
			if (debug) {
				System.out.println("opExpr=" + expr + "  " + name + " ["
						+ begin + "," + end + "]");
			}
			// betweenExpr: where id between 1 and 2
		} else if (expr instanceof SQLInListExpr) {
			SQLInListExpr inListExpr = (SQLInListExpr) expr;
			if (debug) {
				System.out.println("opExpr=" + inListExpr + "   "
						+ inListExpr.getExpr().toString() + " "
						+ inListExpr.getTargetList());
			}
			// inListExpr: where age in(1,2,3)..
		}

	}

	static class Par {
		private String parName;
		private Object value;

		public String getParName() {
			return parName;
		}

		public void setParName(String parName) {
			this.parName = parName;
		}

		public Object getValue() {
			return value;
		}

		public void setValue(Object value) {
			this.value = value;
		}

		@Override
		public String toString() {
			return "Par [parName=" + parName + ", value=" + value + "]";
		}
	}

	static class MyMockResultSet extends MockResultSet {
		private TreeMap<String, Integer> columnLabelToIndex = new TreeMap<String, Integer>(
				String.CASE_INSENSITIVE_ORDER);
		private Field[] fields;
		private boolean isBuildIndex = false;

		public MyMockResultSet(Statement statement) {
			super(statement);
		}

		public MyMockResultSet(Object bean) {
			super(null);
			try {
				fields = bean.getClass().getDeclaredFields();
				Object[] arr = new Object[fields.length];
				List<Object[]> rows = this.getRows();
				for (int i = 0; i < fields.length; i++) {
					Field field = fields[i];
					field.setAccessible(true);
					Object value = field.get(bean);
					arr[i] = value;
					ColumnMetaData metaData = new ColumnMetaData();
					metaData.setColumnName(field.getName());

					meta.getColumns();
				}
				rows.add(arr);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		MockResultSetMetaData meta = new MockResultSetMetaData();

		@Override
		public ResultSetMetaData getMetaData() throws SQLException {
			return super.getMetaData();
		}

		@Override
		public int findColumn(String columnLabel) throws SQLException {
			if (!isBuildIndex) {
				buildIndexMapping();
			}
			return columnLabelToIndex.get(columnLabel);
		}

		public void buildIndexMapping() throws SQLException {
			for (int i = 0; i < fields.length; i++) {
				Field field = fields[i];
				String name = field.getName();
				columnLabelToIndex.put(name, i + 1);
			}
			this.isBuildIndex = true;
		}

	}

}
